﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
//using Oracle.DataAccess.Client;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using trangbaocao.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;
namespace trangbaocao.Models
{
    public class DeviceModel
    {
        public int id { get; set; }
        [Required(ErrorMessage = "Tên thiết bị không được để trống!")]
        public string name { get; set; }
        public int sort_device_id { get; set; }
        public SelectList loai_tb_id { get; set; }

        public int main_sort_device_id { get; set; }
        public SelectList chung_loai_tb_id { get; set; }
        //[Required(ErrorMessage = "Phải chọn nhà sản xuất!")]
        public int manufactorys_id { get; set; }

        //[Required(ErrorMessage = "Phải chọn nhà sản xuất!")]
        public SelectList nsx_id { get; set; }
        public string mo_ta_thiet_bi { get; set; }
        public string ghi_chu { get; set; }


        Sort_DeviceModel Sort_DeviceModel = new Sort_DeviceModel();
        public List<DeviceModel> getDevice()
        {
            List<DeviceModel> device = new List<DeviceModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM device order by name");
            while (odr.Read())
            {
                device.Add(new DeviceModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), main_sort_device_id = Convert.ToInt32(odr[2].ToString()),  sort_device_id= Convert.ToInt32(odr[3].ToString()), manufactorys_id = Convert.ToInt32(odr[4].ToString()), mo_ta_thiet_bi = odr[5].ToString(), ghi_chu = odr[6].ToString() });
            }
            return device;
        }
        public void addDevice(DeviceModel device)
        {
            Sort_DeviceModel s_d = Sort_DeviceModel.getLoaithietbi().Find(delegate(Sort_DeviceModel s) { return s.id == device.sort_device_id; });
            string sql = "INSERT INTO device";
            sql += " select nvl((select max(id)+1 from device),1),'" + device.name + "'," + s_d.main_sort_device_id + ","+device.sort_device_id+","+device.manufactorys_id+",'"+device.mo_ta_thiet_bi+"','"+device.ghi_chu+"' from dual";
            General.Lib_ExecuteNonQuery(sql);
        }
        public void updateDevice(DeviceModel device)
        {
            Sort_DeviceModel s_d = Sort_DeviceModel.getLoaithietbi().Find(delegate(Sort_DeviceModel s) { return s.id == device.sort_device_id; });
            string sql = "UPDATE device SET name='" + device.name + "', main_sort_device_id=" + s_d.main_sort_device_id + ",sort_device_id=" + device.sort_device_id + ",manufactorys_id=" + device.manufactorys_id + ",mo_ta_thiet_bi='" + device.mo_ta_thiet_bi + "',ghi_chu='" + device.ghi_chu + "' where id=" + device.id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public void deleteDevice(int id)
        {
            string sql = "DELETE FROM device WHERE id=" + id;
            General.Lib_ExecuteNonQuery(sql);
        }
        public int checkExist(DeviceModel device)
        {
            string sql = "SELECT * FROM device WHERE name='" + device.name + "'";
            return General.Lib_CheckExist(sql);
        }
        public int checkSubId(int id)
        {
            string sql = "SELECT * FROM Device_Stations WHERE device_id='" + id + "'";
            return General.Lib_CheckExist(sql);
        }
        public List<DeviceModel> FindDevice(string str)
        {
            List<DeviceModel> device = new List<DeviceModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM device WHERE "+str);
            while (odr.Read())
            {
                device.Add(new DeviceModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), main_sort_device_id = Convert.ToInt32(odr[2].ToString()), sort_device_id = Convert.ToInt32(odr[3].ToString()), manufactorys_id = Convert.ToInt32(odr[4].ToString()), mo_ta_thiet_bi = odr[5].ToString(), ghi_chu = odr[6].ToString() });
            }
            return device;
        }
    }
    public class DeviceAssociateModel
    {
        public int id { get; set; }
        public string name { get; set; }
        public string sort_device_name { get; set; }
        public string main_sort_device_name { get; set; }
        public string manufactorys_name { get; set; }
        public string mo_ta_thiet_bi { get; set; }
        public string ghi_chu { get; set; }
        public List<DeviceAssociateModel> getDeviceIndex()
        {
            List<DeviceAssociateModel> device = new List<DeviceAssociateModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.id,a.name,c.name,b.name,d.name,a.mo_ta_thiet_bi,a.ghi_chu FROM device a,main_sort_device b,sort_device c,manufactorys d where a.main_sort_device_id=b.id and a.sort_device_id=c.id and a.manufactorys_id=d.id order by d.name,b.name,c.name,a.name");
            while (odr.Read())
            {
                device.Add(new DeviceAssociateModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), sort_device_name = odr[2].ToString(), main_sort_device_name = odr[3].ToString(), manufactorys_name = odr[4].ToString(), mo_ta_thiet_bi = odr[5].ToString(), ghi_chu = odr[6].ToString() });
            }
            return device;
        }
        public List<DeviceAssociateModel> FindDevice(string str)
        {
            List<DeviceAssociateModel> device = new List<DeviceAssociateModel>();            
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.id,a.name,c.name,b.name,d.name,a.mo_ta_thiet_bi,a.ghi_chu FROM device a,main_sort_device b,sort_device c,manufactorys d where a.main_sort_device_id=b.id and a.sort_device_id=c.id and a.manufactorys_id=d.id and " + str + " order by d.name,b.name,c.name,a.name");
            while (odr.Read())
            {
                device.Add(new DeviceAssociateModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), sort_device_name = odr[2].ToString(), main_sort_device_name = odr[3].ToString(), manufactorys_name = odr[4].ToString(), mo_ta_thiet_bi = odr[5].ToString(), ghi_chu = odr[6].ToString() });
            }
            return device;
        }
    }
}